package org.sixtynine.stock.service.file;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Restrictions;
import org.sixtynine.stock.Constants;
import org.sixtynine.stock.ReturnData;
import org.sixtynine.stock.entity.BaseEntity;
import org.sixtynine.stock.entity.Company;
import org.sixtynine.stock.entity.QuarterlyShareData;
import org.sixtynine.stock.service.GenericService;
import org.sixtynine.stock.service.business.StockUtil;
import org.sixtynine.stock.util.DateUtil;
import org.sixtynine.stock.util.FileUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component(ExcelProcessorFactory.FILE_TYPE_COMPANY_QUARTER_SHARE_DATA_LIST)
public class CompanyQuarterShareListProcessor extends CompanyDataListProcessor {

	Date formatedDate;

	@Autowired
	GenericService genericService;

	private static Map<String, QuarterlyShareData> exsistingDataMap;

	private static void processExsistingRecords(List<BaseEntity> dataList) {
		exsistingDataMap = new HashMap<String, QuarterlyShareData>();

		for (BaseEntity baseEntity : dataList) {
			QuarterlyShareData quarterlyShareData = (QuarterlyShareData) baseEntity;
			exsistingDataMap.put((quarterlyShareData._getFullQuarterCode()),
					quarterlyShareData);
		}
	}

	@Override
	public ReturnData<List<? extends BaseEntity>> process(String filePath) {

		ReturnData<List<? extends BaseEntity>> returnData = new ReturnData<List<? extends BaseEntity>>(
				Constants.SUCCESS, "");

		init();

		FileInputStream fis = null;

		try {
			List<BaseEntity> quarterlyShareDataList = new ArrayList<BaseEntity>();
			File file = new File(filePath);

			fis = new FileInputStream(file);
			// Get the workbook instance for XLS file
			Workbook workbook = WorkbookFactory.create(fis);

			// Get first sheet from the workbook
			Sheet sheet = workbook.getSheetAt(0);

			Row lastCol = null;
			int lastColNum = 0;
			int lastRow = 0;
			if (sheet.getLastRowNum() > 0) {
				lastRow = sheet.getLastRowNum() + 1;
				lastCol = sheet.getRow(sheet.getLastRowNum());

				lastColNum = lastCol.getLastCellNum();
			}

			double profit, weightedAverage, averageEquity, averageTotalAsse, profitAfterTax, revenues, ebitda, cash, percentOwnbyInsiders, instOwnership, sharesInFreefloat, debt, totShareOutstand, equityToSharehol, lstFourQtrWeighted, adjestedEquityShareholder, adjestedTotalAssets, recurringNetProfit, currentAssets, lastFourQtrprofit, currentLiability = 0.00;
			String quarterCode = null;

			String fileName = file.getName();
			String companyCode = fileName.split("_")[0];

			// check file name
			if (!StockUtil.getFileName(fileName).equals(Constants.QUARTER_DATA)) {
				returnData.setNo(Constants.ERROR);
				returnData.setMsg("Please add quarter data only");
				return returnData;
			}

			Company company = companyMap.get(companyCode);

			if (company == null) {
				returnData.setNo(Constants.ERROR);
				returnData.setMsg("Company code " + companyCode
						+ " not found in the database, wrong file name format");
			}

			Criterion criterion = (Restrictions.eq("company.id",
					company.getId()));
			List<BaseEntity> dataList = genericService.findByCriteria(
					criterion, QuarterlyShareData.class);
			if (dataList.size() > 0) {
				processExsistingRecords(dataList);
			}

			if (sheet.getLastRowNum() > 0) {
				for (int i = 0; i < lastColNum; i++) {
					if (i != 0) {
						QuarterlyShareData quarterlyShareData = null;
						// profit = last four quarter recurring profit
						profit = 0.00;
						weightedAverage = 0.00;
						averageEquity = 0.00;
						averageTotalAsse = 0.00;
						profitAfterTax = 0.00;
						revenues = 0.00;
						ebitda = 0.00;
						cash = 0.00;
						percentOwnbyInsiders = 0.00;
						instOwnership = 0.00;
						sharesInFreefloat = 0.00;
						debt = 0.00;
						totShareOutstand = 0.00;
						equityToSharehol = 0.00;
						lstFourQtrWeighted = 0.00;
						adjestedEquityShareholder = 0.00;
						adjestedTotalAssets = 0.00;
						recurringNetProfit = 0.00;
						currentAssets = 0.00;
						currentLiability = 0.00;

						lastFourQtrprofit = 0.00;

						for (int a = 0; a < lastRow; a++) {

							Row row = sheet.getRow(a);
							// Get iterator to all cells of current row
							// Iterator<Cell> cellIterator = row.cellIterator();

							Cell cell = row.getCell(i);
							Object cellVal = getCellValue(cell);
							if (i == 0) {
								String cellValText = (String) cellVal;
							}
							switch (a) {

							case 0:
								quarterCode = (String) cellVal;

								if (checkQtrCode(quarterCode) > 5) {
									returnData.setNo(Constants.ERROR);
									returnData.setMsg("Quarter code "
											+ quarterCode + " is wrong");

								}
								if (checkQtrCode(quarterCode) < 1) {
									returnData.setNo(Constants.ERROR);
									returnData.setMsg("Quarter code "
											+ quarterCode + " is wrong");
								}
								if (checkFutureQtr(quarterCode)) {
									returnData.setNo(Constants.ERROR);
									returnData
											.setMsg("Please enter correct year");
								}

								break;
							case 1:
								revenues = (Double) cellVal;
								break;
							case 2:
								recurringNetProfit = (Double) cellVal;
								break;
							case 3:
								lastFourQtrprofit = (Double) cellVal;
								break;
							case 4:
								profitAfterTax = (Double) cellVal;
								break;
							case 5:
								ebitda = (Double) cellVal;
								break;
							case 6:
								averageTotalAsse = (Double) cellVal;
								break;
							case 7:
								adjestedTotalAssets = (Double) cellVal;
								break;
							case 8:
								cash = (Double) cellVal;
								break;
							case 9:
								currentAssets = (Double) cellVal;
								break;
							case 10:
								currentLiability = (Double) cellVal;
								break;
							case 11:
								debt = (Double) cellVal;
								break;
							case 12:
								averageEquity = (Double) cellVal;
								break;
							case 13:
								equityToSharehol = (Double) cellVal;
								break;
							case 14:
								adjestedEquityShareholder = (Double) cellVal;
								break;
							case 15:
								weightedAverage = (Double) cellVal;
								break;
							case 16:
								lstFourQtrWeighted = (Double) cellVal;
								break;
							case 17:
								totShareOutstand = (Double) cellVal;
								break;
							case 18:
								sharesInFreefloat = (Double) cellVal;
								break;
							case 19:
								percentOwnbyInsiders = (Double) cellVal;
								break;
							case 20:
								instOwnership = (Double) cellVal;
								break;

							default:
								break;
							}

						}

						if (company == null) {
							returnData.setNo(Constants.ERROR);
							returnData
									.setMsg("Company Code cannot find for company code "
											+ companyCode);

							return returnData;
						}
						if (dataList.size() > 0) {
							if (exsistingDataMap.containsKey(quarterCode)) {
								if (exsistingDataMap.get(quarterCode)
										.getCompany().getId() == company
										.getId()) {
									quarterlyShareData = exsistingDataMap
											.get(quarterCode);
								} else {
									quarterlyShareData = new QuarterlyShareData();
								}

							} else {
								quarterlyShareData = new QuarterlyShareData();
							}
						} else {
							quarterlyShareData = new QuarterlyShareData();
						}

						quarterlyShareData.setCompany(company);
						quarterlyShareData.setAverageEquity(averageEquity);
						quarterlyShareData
								.setAverageTotalAsse(averageTotalAsse);
						quarterlyShareData.setCash(cash);
						quarterlyShareData.setDebt(debt);
						quarterlyShareData.setEbitda(ebitda);
						quarterlyShareData.setInstOwnership(instOwnership);
						quarterlyShareData
								.setPercentOwnbyInsiders(percentOwnbyInsiders);
						quarterlyShareData.setProfit(profit);
						quarterlyShareData.setProfitAfterTax(profitAfterTax);
						quarterlyShareData.setRevenues(revenues);
						quarterlyShareData
								.setSharesInFreefloat(sharesInFreefloat);
						quarterlyShareData.setWeightedAverage(weightedAverage);
						quarterlyShareData
								.setQuarterCode(getCodeByQtrCode(quarterCode));
						quarterlyShareData
								.setYear(getYearByQtrCode(quarterCode));
						quarterlyShareData
								.setTotShareOutstand(totShareOutstand);
						quarterlyShareData
								.setEquityToSharehol(equityToSharehol);
						quarterlyShareData
								.setLstFourQtrWeighted(lstFourQtrWeighted);
						quarterlyShareData
								.setAdjestedEquityShareholder(adjestedEquityShareholder);
						quarterlyShareData
								.setAdjestedTotalAssets(adjestedTotalAssets);
						quarterlyShareData
								.setRecurringNetProfit(recurringNetProfit);
						quarterlyShareData.setCurrentAssets(currentAssets);
						quarterlyShareData
								.setCurrentLiability(currentLiability);

						quarterlyShareData
								.setLstFourQtrRecurringProfit(lastFourQtrprofit);

						quarterlyShareDataList.add(quarterlyShareData);

					}
				}
				returnData.setData(quarterlyShareDataList);

			}

		} catch (IOException e) {
			e.printStackTrace();
			returnData.setNo(Constants.ERROR);
			returnData.setMsg(e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			returnData.setNo(Constants.ERROR);
			returnData.setMsg(e.getMessage());
		} finally {
			FileUtil.close(fis);
		}

		return returnData;
	}

	public int getYearByQtrCode(String code) {
		int year = Integer.parseInt(code.split("_")[0]);
		return year;

	}

	public String getCodeByQtrCode(String code) {
		return code.split("_")[1];
	}

	public int checkQtrCode(String code) {
		String qtrCode = code.split("_")[1];
		String qtr = qtrCode.substring(1);

		return Integer.parseInt(qtr);
	}

	public boolean checkFutureQtr(String code) {

		int year = Integer.parseInt(code.split("_")[0]);
		int currentYear = DateUtil.getYearFromDate(new Date());

		return currentYear < year;
	}
}
